From 0b2742a8fd4456b5c6218424eeee769c1e6cc2ee Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Sun, 26 Jul 2026 05:11:47 +0000 Subject: [PATCH] Restructure docs for the novice and add more tutorial-like information Restructure docs so the reader is guided through the initial chapters Add more tutorial-like documenation for the new reader. --- doc/src/architecture.m4 | 4 +- doc/src/architecture/timestamps.m4 | 34 +++++ doc/src/epilog.inc.m4 | 3 + doc/src/index.m4 | 6 +- doc/src/intro.m4 | 4 +- doc/src/intro/about_db.m4 | 21 +++- doc/src/intro/about_doc.m4 | 51 +++++++- doc/src/views.m4 | 196 +++++++++++++++++++++++++++-- doc/src/views/biography.m4 | 49 +++++++- 9 files changed, 336 insertions(+), 32 deletions(-) diff --git a/doc/src/architecture.m4 b/doc/src/architecture.m4 index 28b0e5f..6b86d54 100644 --- a/doc/src/architecture.m4 +++ b/doc/src/architecture.m4 @@ -1,4 +1,4 @@ -.. Copyright (C) 2023 The Meme Factory, Inc. www.karlpinc.com +.. Copyright (C) 2023, 2026 The Meme Factory, Inc. www.karlpinc.com This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as @@ -20,6 +20,8 @@ sdb_rst_quotes(`on')dnl sdb_generated_rst()dnl +.. _system architecture: + System Architecture =================== diff --git a/doc/src/architecture/timestamps.m4 b/doc/src/architecture/timestamps.m4 index ee45bd2..d2549b7 100644 --- a/doc/src/architecture/timestamps.m4 +++ b/doc/src/architecture/timestamps.m4 @@ -102,6 +102,40 @@ To sum up, most of the time-related values you work with will be in Gombe time. The rest are in ``sdb_timezone`` unless you put some work into changing your time zone. +.. timestamps_and_absolute_time_comparisons: + +TIMESTAMPS and Absolute Time Comparisons +```````````````````````````````````````` + +The ``TIMESTAMP`` stores a date and a time as a single unit of data. +In other words, it records a point in time that is absolute. +A time recorded as a ``TIMESTAMP`` value contains everything you can +know about the time value itself. + +Although SokweDB contains few, if any, ``TIMESTAMP`` data types it can +be convenient to produce them when you want to compare absolute times. +Data recorded as ``DATE`` and ``TIME`` data types can be converted to +a ``TIMESTAMP`` value by adding the time to the date. + +For example, to find |DYADS| rows that involve events that started +before |OBS| rows' events started, there are the following two +approaches, presented here as fragments of a (nonsensical, as it +stands) hypothetical SQL query: + +.. code-block:: sql + :caption: + Comparing absolute start time values using dates and times + + WHERE (dyads.date < obs.date + OR (dyads.date = obs.date AND dyads.start < obs.start)) + + +.. code-block:: sql + :caption: + Comparing absolute start time values using timestamps + + WHERE (dyads.date + dyads.start < obs.date + obs.start) + .. rubric: Footnotes .. [#f1] The string is said to be `cast` to the desired data type. diff --git a/doc/src/epilog.inc.m4 b/doc/src/epilog.inc.m4 index f61652b..3d1d67f 100644 --- a/doc/src/epilog.inc.m4 +++ b/doc/src/epilog.inc.m4 @@ -42,6 +42,9 @@ sdb_generated_rst()dnl .. _double precision floating point number: https://en.wikipedia.org/wiki/Double_precision_float +.. _views: + https://www.postgresql.org/docs/current/tutorial-views.html + .. _UUID: https://en.wikipedia.org/wiki/Universally_unique_identifier .. _technical debt: https://en.wiktionary.org/wiki/technical_debt diff --git a/doc/src/index.m4 b/doc/src/index.m4 index b95fe13..f200beb 100644 --- a/doc/src/index.m4 +++ b/doc/src/index.m4 @@ -1,4 +1,4 @@ -.. Copyright (C) 2019, 2023 The Meme Factory, Inc. www.karlpinc.com +.. Copyright (C) 2019, 2023, 2026 The Meme Factory, Inc. www.karlpinc.com This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as @@ -32,11 +32,11 @@ Technical Documentation intro.rst architecture.rst er_diagrams.rst - code_tables.rst + views.rst tables.rst analyzed.rst housekeeping.rst - views.rst + code_tables.rst functions.rst appendices.rst diff --git a/doc/src/intro.m4 b/doc/src/intro.m4 index 4400049..14710a8 100644 --- a/doc/src/intro.m4 +++ b/doc/src/intro.m4 @@ -1,4 +1,4 @@ -.. Copyright (C) 2023 The Meme Factory, Inc. www.karlpinc.com +.. Copyright (C) 2023, 2026 The Meme Factory, Inc. www.karlpinc.com This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as @@ -19,6 +19,8 @@ include(macros.m4)dnl sdb_rst_quotes(`on')dnl sdb_generated_rst()dnl +.. _introduction: + Introduction ============ diff --git a/doc/src/intro/about_db.m4 b/doc/src/intro/about_db.m4 index baca6a0..b044837 100644 --- a/doc/src/intro/about_db.m4 +++ b/doc/src/intro/about_db.m4 @@ -1,4 +1,5 @@ -.. Copyright (C) 2019, 2020, 2023 The Meme Factory, Inc. www.karlpinc.com +.. Copyright (C) 2019, 2020, 2023, 2026 The Meme Factory, Inc. + www.karlpinc.com This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as @@ -19,6 +20,8 @@ include(macros.m4)dnl sdb_rst_quotes(`on')dnl sdb_generated_rst()dnl +.. _system design: + System Design ------------- @@ -40,8 +43,9 @@ web-page development, maximizes availability. The primary method of interacting with the database is SQL, the industry standard relational query language. The amount of SQL which must be learned can be, depending on the amount of development effort spent, reduced to an -absolute minimum through the use of :ref:`views ` -- in short, -pre-packaged queries. + +absolute minimum through the use of :ref:`views ` -- in +short, pre-packaged queries. Data is kept secure though industry-standard practices. These include the encryption of communications, the association of accounts with @@ -101,7 +105,7 @@ multiple rows. Column names are singular, as each column of each table holds a single value. -.. _views: +.. _about views: About Views ``````````` @@ -180,6 +184,15 @@ that may involve discovering and correcting errors in the data when bulk-loading. +.. _database architecture: + +Database Architecture +````````````````````` + +An overview of the database's architecture is found in the :ref:`views +and sokwedbs structure` section of the documentation. + + .. rubric:: Footnotes .. [#f1] The term "schema" is overloaded. A separate meaning defines diff --git a/doc/src/intro/about_doc.m4 b/doc/src/intro/about_doc.m4 index 5621fd0..f7e59ab 100644 --- a/doc/src/intro/about_doc.m4 +++ b/doc/src/intro/about_doc.m4 @@ -1,4 +1,5 @@ -.. Copyright (C) 2019, 2020, 2023 The Meme Factory, Inc. www.karlpinc.com +.. Copyright (C) 2019, 2020, 2023, 2026 The Meme Factory, Inc. + www.karlpinc.com This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as @@ -76,6 +77,48 @@ A Guide for the Reader This is a reference document, and as such is not expected to be read from front-to-back. +That said, the recommenced approach for someone new to SokweDB is to: + +* Skim thorough the next section's (:ref:`system design`) overview, + but read all the detail in the **About** sub-sections. + +* Read the section and sub-section headings in the :ref:`system + architecture` section. + Read in full whatever (sub-)sections are of interest, but at least + scan the :ref:`databases` and :ref:`schemas` sections. + +* Scan briefly the |ER Diagrams|\ , and the key, only to get a sense + of what that part of the documentation looks like. + + Refer back to the diagrams when necessary while reading the rest of + the documentation + +* Read the entirety of the :ref:`views and sokwedbs structure` + section, its sub-sections, and the documentation of the various + views found therein. + + The :ref:`views and sokwedbs structure` section is different from + the rest of the documentation in that it is, in some way, a + tutorial. + Rather than present views alphabetically, this section's table of + contents structures the presentation order to guide the reader's + understanding of the entire system. + + Further, the SQL examples presented in the section contain techniques + that may be useful when querying database content, and can be + modified to produce results that are useful. + +* Read the introductory paragraph of each table in the + :ref:`data_tables` section to familiarize yourself with the broad + outlines of where data is kept. + +After that, the reader should have enough background to explore +whatever other parts of the documentation that are of interest, +when they become of interest. + +It wouldn't hurt to try logging in to the database and executing +example queries while reading. + Care must be taken when querying columns which allow |null| values. SQL uses a `three valued logic `_, the values being |true|, |false|, and |null|. This only comes into play when a |null| value is @@ -95,8 +138,8 @@ query relates multiple tables. .. [#f4] Written |null| in SQL. -.. [#f5] The notations in the :ref:`erdiagrams` are dense in - constraints, constraints built into the database, which - ensure data integrity. +.. [#f5] The notations in the |ER Diagrams| are dense in constraints, + constraints built into the database, which ensure data + integrity. .. _SQL logic: https://www.postgresql.org/docs/current/functions-logical.html diff --git a/doc/src/views.m4 b/doc/src/views.m4 index ae3b5bb..97bb1a7 100644 --- a/doc/src/views.m4 +++ b/doc/src/views.m4 @@ -20,6 +20,141 @@ sdb_rst_quotes(`on')dnl sdb_generated_rst()dnl +.. _views and sokwedbs structure: + +Views and SokweDB's Structure +============================= + +As mentioned in the :ref:`introduction`, views_ are queries that look +like tables. +As such, they can, and usually do, combine multiple tables into +something that looks like a single table. + +Views are important to SokweDB because they appear to transform the +underlying structure of the database's tables into something more akin +to the traditional paper data collection sheets used to collect data in +the field. + +To understand what this means we'll look at grooming and aggression. + +The paper data collection sheets have separate columns for the +individual doing the grooming and the individual receiving the +grooming. +Or, similarly, the individual aggressing and the individual aggressed +against. +These two different types of data could each be stored in separate +tables, with columns in the aggressions table for aggressor and +aggressee, and columns in the grooming table for groomer and groomee. + +This organizational structure works well when one wants to query each +of these behaviors in isolation. +It begins to break down when querying across data sets, particularly +as the number of data sets increases beyond a few. + +To ask a simple question like "when was the last time we observed +individual A", or "how many times has individual A been involved in an +interaction with individual B", one must combine the results of +querying all the behavioral tables. +This is simple when there's only two types of data we're collecting, +aggressions and groomings, but becomes increasingly complicated as +more categories of data are collected. + +To solve this problem SokweDB structures the database around +:dfn:`events`. +Events are times when data is collected. +An event may record a behavior, an individual's location, or even be +something like a record of what was recorded in ad-hoc textual notes. +Events are categorized by :dfn:`behavior`. +A grooming event has its own behavior, an aggression event has it's +own behavior, and so forth. + +Because an event can be one of so many different things, an event's +behavior code does not necessarily correspond with a behavior that a +chimpanzee might exhibit. +But the terminology works well enough. +Queries specify an event's behavior to report on a specific chimpanzee +behavior. + +Events are organized into :dfn:`watches`, a period of time when an +observer was observing -- observing *something*. + +Very many watches are follows, so all the behaviors recorded during a +single follow of a focal are events that are related to a single +watch. + +Most other watches are records of an observation of a chimpanzee at +the feeding station. +In these cases there's no actual follow, but the individual observed +"takes the place" of the focal. + +And some watches are just ad-hoc observations. +For example, a pantgrunt observed by happenstance. +In these cases a watch may be related to only one event. + +Watches are categorized by :dfn:`type`. +These map to things like follows, feeding station observations, and +the recording of chimpanzee locations. +Queries specify a watch's type to report on a specific data collection +protocol, like a follow. + +Individual chimpanzees are associated with events. +Each individual plays a role in the event. +Like a role in a theater play, where an actor may play a different +role in different performances, chimpanzees play different roles in +different events. +Any event can be associated with multiple roles, each played by a +different chimpanzee. + +Each role, associated with some specific event, designates both a +chimpanzee and, by way of a role code, the role the chimpanzee plays +in the event. + +Some roles are "special", the system knows they have special meaning. + +Most (if not all) roles associated with dyadic events, pairwise events +like groomings or aggressions, have special meaning. + +In the case of groomings and aggressions we only mention here two +of the special role codes, although there are others. +These are the special role codes ``sdb_actor`` and ``sdb_actee``. +The individual aggressing and the individual performing the grooming have the ``sdb_actor`` role. +The individual receiving the aggression and the individual receiving +the grooming have the ``sdb_actee`` role. + +By making these two role codes "generic" and using them in both the +case of grooming and the case of aggression, instead of having four +codes, "groomer" and "groomee" and "aggressor" and "aggressee", we can +more easily query across behaviors. +It becomes simple to ask questions, irrespective of behavior, about +those receiving behaviors and those performing them. + +The structure of the tables that hold the SokweDB support are very +flexible in the kinds of data they may hold. +It takes little in the way of modification to record hitherto +unrecorded behaviors or to use new data collection protocols. +And the real benefit is the relative ease of writing queries that +return cross-behavioral or cross protocol information. + +The cost is an increase in the complexity of writing a query that +returns information on a single behavioral category, collected using a +single protocol, like groomings involving the focal collected during a +follow. + +But views, which are relatively easy to create and maintain, can +entirely remove the barrier to querying on a single behavioral +category.\ [#f1]_ +They do this by appearing to be a table, that looks just like a table +that holds data on a single behavioral category. + +The |PANTGRUNTS_VIEW| view is a good example of this.\ [#f2]_ +It combines the all the underlying data kept in separate tables, and, +in the case of roles, data that's kept in separate rows -- data that's +specifically pantgrunt related like whether there were multiple +pantgrunt recipients, with the chimpanzees that play the roles of +pantgrunter and pantgrunt receiver, with the time of the event, and +with the date and focal (if the watch is a follow) of the watch --, +into what looks and acts just like a regular table. + .. _data_retrieval_views: Data Retrieval Views @@ -31,18 +166,27 @@ in the old MS Access database. Others reproduce common query patterns, eliminating the need to connect (join) multiple tables. -+-------------------+----------------------+----------------------+-------------------+ -| View | One row for each | Purpose | Tables/Views used | -+===================+======================+======================+===================+ -| |BIOGRAPHY| | |BIOGRAPHY_DATA| row | Reproduce "old" data | |BIOGRAPHY_DATA| | -| | | data | | -+-------------------+----------------------+----------------------+-------------------+ -| |BREC_NOTES_VIEW| | |BRECORD_NOTES| row | Simplify queries | |OBS|, | -| | | that join | |BRECORD_NOTES| | -| | | |BRECORD_NOTES| | | -| | | with other |EVENTS| | | -| | | related tables | | -+-------------------+----------------------+----------------------+-------------------+ +.. Configure the Purpose column to be 25% of the line length. + Otherwise, the table names can be hyphenated and contain line breaks + and the table can overflow the right margin in PDFs. +.. tabularcolumns:: l l \Y{.25} l + +.. table:: The columns of the OBS view + :widths: auto + :class: longtable + + +-------------------+----------------------+----------------------+-------------------+ + | View | One row for each | Purpose | Tables/Views used | + +===================+======================+======================+===================+ + | |BIOGRAPHY| | |BIOGRAPHY_DATA| row | Reproduce "old" data | |BIOGRAPHY_DATA| | + | | | data | | + +-------------------+----------------------+----------------------+-------------------+ + | |BREC_NOTES_VIEW| | |BRECORD_NOTES| row | Simplify queries | |OBS|, | + | | | that join | |BRECORD_NOTES| | + | | | |BRECORD_NOTES| | | + | | | with other |EVENTS| | | + | | | related tables | | + +-------------------+----------------------+----------------------+-------------------+ .. toctree:: :maxdepth: 3 @@ -87,6 +231,32 @@ Others reproduce common query patterns, eliminating the need to connect .. toctree:: :maxdepth: 3 - views/dyads.rst views/obs.rst + views/dyads.rst views/pantgrunts_view.rst + + +.. rubric:: Footnotes + +.. [#f1] + While it is relatively easy to write a query, or create a view, + that combines the more generic tables containing watches, events, + roles, and so forth, and then selects from the result a specific + behavior, it is much more tedious and error prone to do the + reverse. + + Writing queries that, in effect, concatenate data that appears in + different forms in different tables, each of which are specialized + to hold only a very specific behavior, can get complicated quickly. + + It is sometimes useful to be able to easily ask questions that + aggregate across behaviors. + SokweDB is designed so that it is not overly hard to ask + complicated questions, and views make asking simple questions easy + too. + +.. [#f2] + At the time of this writing there are not views for every behavior. + Should more views be needed to support queries on other behaviors, + the code behind the |PANTGRUNTS_VIEW| view serves as an example + that can be easily adapted. diff --git a/doc/src/views/biography.m4 b/doc/src/views/biography.m4 index 5427f85..4766335 100644 --- a/doc/src/views/biography.m4 +++ b/doc/src/views/biography.m4 @@ -1,4 +1,4 @@ -.. Copyright (C) 2024 The Meme Factory, Inc. www.karlpinc.com +.. Copyright (C) 2024, 2026 The Meme Factory, Inc. www.karlpinc.com This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as @@ -29,14 +29,37 @@ BIOGRAPHY corresponding |BIOGRAPHY_DATA| row, making the data more like the traditional format and therefore, in one sense, easier to work with. -|BIOGRAPHY_summary| -This view contains one row for each chimpanzee on -which data has ever been recorded (in SokweDB), and an additional -row for ``sdb_unk`` a generic value used when a chimpanzee is -unrecognized. +|BIOGRAPHY_summary| This view (like the underlying |BIOGRAPHY_DATA| +table itself) contains one row for each chimpanzee on which data has +ever been recorded (in SokweDB), and an additional row for ``sdb_unk`` +a generic value used when a chimpanzee is unrecognized. BIOGRAPHY contains the basic demographic data of individual chimpanzees. +BIOGRAPHY is a simple view, consisting of a single table along with a +few expressions which construct data to be presented in computed +(virtual, in a sense) columns. +In this spirit, here is a relatively simple query. +(It does contain a computed column, and does use a computation to +determine sort order.\ [#f1]_) +It reports on males, sorting them first by whether or not they are +alive, missing, known to be dead, etc., and within that by age, and +within that by name. + +.. code-block:: sql + :caption: + Males, by whether they are alive (or missing, or etc.), by age, by name + + SELECT biography.animid, biography.departtype, biography.animname + , biography.birthdate + , (biography.departdate - biography.birthdate) / 365.25 + AS years_old + FROM biography + WHERE sex = 'sdb_male' + ORDER BY biography.departtype + , (biography.departdate - biography.birthdate) + , biography.animname; + .. _BIOGRAPHY_Definition: @@ -109,3 +132,17 @@ Operations Allowed None. +.. rubric:: Footnotes + +.. [#f1] + The computation leverages the fact that, when performing addition + and subtraction operations on the ``DATE`` data type, the result is + an integer -- a number of days. + This makes it easy to compute an age in days, and to convert that + to other units. + + Other data types, like date-and-time-together ``TIMESTAMP`` data + type, do not have this feature. + In those cases the |function_julian| function, which returns a + "date" that is counted in units of days, is a convient way to + replicate this kind of computation. -- 2.34.1